package edu.cmu.aes.project2.wshelper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;

import edu.cmu.aes.share.MySqlProperties;

public class Warehouse {

	private static String databaseLocation = MySqlProperties.getURL()+"/aes_project2_warehouse";
	private static String userName = MySqlProperties.getUserName();
	private static String password = MySqlProperties.getPassword();
	
	//private static String fileLocation = "D:/AllWorkSpace/workspace-aes/Supplier/Input/consolidated_data.xml";
	//private static String fileLocation = "Input/consolidated_data.xml";

	private static Connection getConnection() throws SQLException {
		Connection connection = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(databaseLocation,
					userName, password);
		} catch (Exception e) {
			throw new SQLException("Cannot instantiate database connector");
		}
		return connection;
	}
	
	public boolean instock(){		
		int choice = (int)(Math.random()*2);
		if(choice == 0)
			return false;
		else return true;
	}
	
public boolean orderApprove(Order order) {
		
		
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		OrderItems[] orderitems = order.getOrder();
		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
			
			try{
			PreparedStatement pstmnt = conn.prepareStatement("insert into orders(customer_name,amount,date) values (?,?,?)");
			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setInt(2, order.getAmount());
			pstmnt.setDate(3, sqlDate);
			int result = pstmnt.executeUpdate();
			
			
			pstmnt = conn.prepareStatement("select * from orders order by order_id desc limit 1");
			ResultSet resultset = pstmnt.executeQuery();
			
			int orderid = 0;
			if(resultset.next())
			orderid = Integer.parseInt(resultset.getString("order_id"));
			
			for(int i=0;i<orderitems.length;i++){	
		pstmnt = conn.prepareStatement("insert into orderitems(brand_name,model_name,quantity,order_id) values (?,?,?,?)");
		pstmnt.setString(1, orderitems[i].getBrandName());
		pstmnt.setString(2, orderitems[i].getModelName());
		pstmnt.setInt(3, orderitems[i].getQuantity());
		pstmnt.setInt(4,(orderid));
		result = pstmnt.executeUpdate();
		System.out.println(result);
			}
			}
			catch (Exception e){
				System.out.println(e);
				return false;
			}
		
		
		return true;
		
	}

public boolean orderApproveRollback(Order order) {
		
		
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		OrderItems[] orderitems = order.getOrder();
		java.sql.Date sqlDate = new java.sql.Date(order.getDate().getTime());
			
			try{
			PreparedStatement pstmnt;
			
			
			pstmnt = conn.prepareStatement("select * from orders where customer_name = ? and date = ?");
			pstmnt.setString(1, order.getCustomerName());
			pstmnt.setDate(2, sqlDate);
			ResultSet resultset = pstmnt.executeQuery();
			
			
			int orderid = 0;
			if(resultset.next())
			orderid = Integer.parseInt(resultset.getString("order_id"));
			
			pstmnt = conn.prepareStatement("delete from orders where order_id = ?");
			pstmnt.setInt(1, orderid);
			int result = pstmnt.executeUpdate();
			
			
			
			
			for(int i=0;i<orderitems.length;i++){	
		pstmnt = conn.prepareStatement("delete from orderitems where order_id = ?");
		pstmnt.setInt(1, orderid);
		result = pstmnt.executeUpdate();
		System.out.println(result);
			}
			}
			catch (Exception e){
				System.out.println(e);
				return false;
			}
		
		
		return true;
		
	}


}
